#ifndef DATABASE_HPP
#define DATABASE_HPP

#include <QCheckBox>
#include <QComboBox>
#include <QDebug>
#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>

#define TABLENAME "list"

typedef struct
{
    int Id = 0;
    int ParentId = 0;
    int Statu = 0; //todo,doing,done
    int Priority = 0; //high,normal,low
    int Type = 0; //PROJECT;EPIC,FEATURE,STORY;TASK,BUG
    uint64_t CreateTime = 0;
    uint64_t DoneTime = 0;
    uint64_t StartTime = 0;
    uint64_t StopTime = 0;
    QString Title;
    QString DoUser;
    QString CreateUser;
    QString Note;
    QString tablename;
} data_t;

static inline int getColumnIndex(QString columntxt)
{
    QStringList m_columnname = { "Id",
        "ParentId",
        "Statu",
        "Priority",
        "Type",
        "CreateTime",
        "DoneTime",
        "StartTime",
        "StopTime",
        "Title",
        "DoUser",
        "CreateUser",
        "Note" };

    //根据数据库列标题名称获得列号
    return m_columnname.indexOf(columntxt);
}

//数据库操作
class CDatabase
{
private:
    QSqlDatabase m_db;

public:
    CDatabase(const char *dbname = "Single.db")
    {
        m_db = QSqlDatabase::addDatabase("QSQLITE");
        m_db.setDatabaseName(QString("%1").arg(dbname));

        if (!m_db.open())
        {
            qDebug() << "open database is error\n"
                     << m_db.lastError().text();
            QMessageBox::warning(0, QObject::tr("Database Error"),
                m_db.lastError().text());
            return;
        }

        QSqlQuery query;
        bool bsuccess = query.exec("CREATE TABLE list ("
                                   "Id INTEGER primary key autoincrement,"
                                   "ParentId INTEGER,"
                                   "Statu INTEGER,"
                                   "Priority INTEGER,"
                                   "Type INTEGER,"
                                   "CreateTime INTEGER,"
                                   "DoneTime INTEGER,"
                                   "StartTime INTEGER,"
                                   "StopTime INTEGER,"
                                   "Title TEXT,"
                                   "DoUser TEXT,"
                                   "CreateUser TEXT,"
                                   "Note TEXT"
                                   ")");
        if (!bsuccess)
        {
            qDebug() << "create table: " << query.lastError().text();
        }

        return;
    }

    ~CDatabase()
    {
        if (m_db.isOpen())
            m_db.close();
    }

    void queryBindData(QSqlQuery query, const data_t &data)
    {
        query.bindValue(":ParentId", data.ParentId);
        query.bindValue(":Statu", data.Statu);
        query.bindValue(":Priority", data.Priority);
        query.bindValue(":Type", data.Type);
        query.bindValue(":Title", data.Title);
        query.bindValue(":DoUser", data.DoUser);
        query.bindValue(":CreateUser", data.CreateUser);
        query.bindValue(":CreateTime", data.CreateTime);
        query.bindValue(":StartTime", data.StartTime);
        query.bindValue(":StopTime", data.StopTime);
        query.bindValue(":DoneTime", data.DoneTime);
        query.bindValue(":Note", data.Note);
    }

    void queryToData(const QSqlQuery &query, data_t &data)
    {
        data.Id = query.value(getColumnIndex("Id")).toInt();
        data.Note = query.value(getColumnIndex("Note")).toString();
        data.Type = query.value(getColumnIndex("Type")).toInt();
        data.Statu = query.value(getColumnIndex("Statu")).toInt();
        data.Title = query.value(getColumnIndex("Title")).toString();
        data.DoUser = query.value(getColumnIndex("DoUser")).toString();
        data.DoneTime = query.value(getColumnIndex("DoneTime")).toULongLong();
        data.ParentId = query.value(getColumnIndex("ParentId")).toInt();
        data.Priority = query.value(getColumnIndex("Priority")).toInt();
        data.StopTime = query.value(getColumnIndex("StopTime")).toULongLong();
        data.StartTime = query.value(getColumnIndex("StartTime")).toULongLong();
        data.CreateTime = query.value(getColumnIndex("CreateTime")).toULongLong();
        data.CreateUser = query.value(getColumnIndex("CreateUser")).toULongLong();
    }

    //读取时间段内容
    void readTilte(uint64_t start, uint64_t stop, QStringList &val)
    {
        data_t data;
        QSqlQuery query;
        query.exec(QString("SELECT * FROM %1 WHERE DoneTime>=%2 AND DoneTime<=%3")
                       .arg(TABLENAME)
                       .arg(start)
                       .arg(stop));
        val.clear();
        while (query.next())
        {
            queryToData(query, data);
            val.append(data.Title);
        }
    }

    //获得父编号下某类型的所有标题（格式：tis_title）
    void getTitleByParent(int parentId, int childType, QStringList &infos, QStringList &titles)
    {
        QSqlQuery query;
        data_t data;

        titles.clear();
        infos.clear();
        query.exec(QString("SELECT * FROM %1 WHERE Type=%2 AND ParentId=%3")
                       .arg(TABLENAME)
                       .arg(childType)
                       .arg(parentId));
        while (query.next())
        {
            queryToData(query, data);
            titles.append(data.Title);
            infos.append(QString("%1##_%2##_%3##_%4")
                             .arg(data.Id)
                             .arg(data.Title)
                             .arg(data.StartTime)
                             .arg(data.StopTime));
        }
    }

    void getParent(int parentId, QList<data_t> &list)
    {
        QSqlQuery query;
        data_t data;

        query.exec(QString("SELECT * FROM %1 WHERE Id=%2")
                       .arg(TABLENAME)
                       .arg(parentId));
        if (query.next())
        {
            queryToData(query, data);
            list.prepend(data);
            getParent(data.ParentId, list);
        }
        else
            return;
    }

    void getChild(int id, QList<data_t> &list)
    {
        QSqlQuery query;
        data_t data;

        list.clear();
        query.exec(QString("SELECT * FROM %1 WHERE ParentId=%2")
                       .arg(TABLENAME)
                       .arg(id));
        while (query.next())
        {
            queryToData(query, data);
            list.append(data);
        }
    }

    //插入一条记录
    bool insert(const data_t &data)
    {
        QSqlQuery query;
        query.prepare(QString("insert into "
                              "%1(ParentId, Statu, Priority, Type, Title, DoUser, CreateUser, DoneTime, CreateTime, StartTime, StopTime, Note) "
                              "values(:ParentId, :Statu, :Priority, :Type, :Title, :DoUser, :CreateUser, :DoneTime, :CreateTime, :StartTime, :StopTime, :Note)")
                          .arg(data.tablename));
        queryBindData(query, data);
        if (!query.exec())
        {
            qDebug() << QString(QObject::tr("insert into failed: ")) + query.lastError().text();
            return false;
        }

        return true;
    }

    //读取时间段内容
    bool readById(data_t &data)
    {
        QSqlQuery query;
        query.exec(QString("SELECT * FROM %1 WHERE Id=%2").arg(data.tablename).arg(data.Id));
        while (query.next())
        {
            queryToData(query, data);
            return true;
        }
        return false;
    }

    //根据ID删除记录
    bool deleteById(data_t &data)
    {
        QSqlQuery query;
        bool bsuccess = query.exec(QString("DELETE FROM %1 WHERE id = %2").arg(data.tablename).arg(data.Id));
        if (!bsuccess)
        {
            qDebug() << QString(QObject::tr("delete into failed: ")) + query.lastError().text();
            return false;
        }
        return true;
    }

    //修改内容
    bool updateById(data_t &data)
    {
        QSqlQuery query;
        query.prepare(QString("UPDATE %1 SET "
                              "ParentId = :ParentId "
                              ",Statu = :Statu "
                              ",Priority = :Priority "
                              ",Type = :Type "
                              ",Title = :Title "
                              ",DoUser = :DoUser "
                              ",CreateUser = :CreateUser "
                              ",CreateTime = :CreateTime "
                              ",StartTime = :StartTime "
                              ",StopTime = :StopTime "
                              ",DoneTime = :DoneTime "
                              ",Note = :Note "
                              "WHERE id=%2")
                          .arg(data.tablename)
                          .arg(data.Id));
        queryBindData(query, data);
        bool success = query.exec();
        if (!success)
        {
            qDebug() << QString(QObject::tr("update into failed: ")) + query.lastError().text();
            return false;
        }
        return true;
    }
};

//类型代码转文字
class TypeIndex
{
    QStringList m_titlename = { "PROJECT", "EPIC", "FEATURE", "STORY", "TASK", "BUG" };
    QList<QColor> m_color = { Qt::white, QColor(0xFF, 0xB9, 0x31), QColor(0x9c, 0xc3, 0x9a), QColor(0xcc, 0xdd, 0xef), Qt::black, Qt::black };

public:
    QString toString(int index)
    {
        if (index > m_titlename.length())
        {
            qDebug() << "index > list.length!!";
            return "";
        }
        return m_titlename[index];
    }
    QColor toBackgroundColor(int index)
    {
        if (index > m_titlename.length())
        {
            qDebug() << "index > list.length!!";
            return m_color[0];
        }
        return m_color[index];
    }
};

//状态代码转文字
class StatuIndex
{
    QStringList m_statname = { "待开始", "进行中", "已完成" };
    QList<QBrush> m_statucolor = { Qt::darkRed, Qt::darkGreen, Qt::gray };

public:
    void initComboBox(QComboBox *cbox, int index)
    {
        cbox->clear();
        cbox->addItems(m_statname);
        cbox->setCurrentIndex(index);
    }
    QString toString(int index)
    {
        if (index > m_statname.length())
        {
            qDebug() << "index > list.length!!";
            return "";
        }
        return m_statname[index];
    }
    QBrush toColor(int index)
    {
        if (index > m_statucolor.length())
        {
            qDebug() << "index > list.length!!";
            return Qt::black;
        }
        return m_statucolor[index];
    }
};

#endif // DATABASE_HPP
